package myJDBC.lowLevel;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import myJDBC.myConnection;

public class References {

	public static void createReference(Integer attrId, Integer objId, Integer refId) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			pstmt = myConnection.dbConnection
					.prepareStatement("insert into \"references\"(\"attribute_id\",\"object_id\",\"reference\") values (?,?,?)");
			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.setInt(3, refId);
			pstmt.executeUpdate();
		} finally {
			if (pstmt != null)
				pstmt.close();
		}	
	}
	
	public static void deleteReference(Integer attrId, Integer objId) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			pstmt = myConnection.dbConnection
					.prepareStatement("DELETE FROM \"references\" WHERE \"attribute_id\" = ? and \"object_id\"=? ");
			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.executeUpdate();
		} finally {
			if (pstmt != null)
				pstmt.close();
		}	
	}
	
	public static ArrayList<String> returnNameObj(int attrId, int objId) throws SQLException {
		ArrayList<String> al = new ArrayList<String>();
		String tmp="";
		StringBuilder selectAttributes = new StringBuilder();
	    selectAttributes.append("select \"object_id\", \"name\" ")
					    .append("from \"objects\" ")
					    .append("where \"object_id\" in(")
					    .append("select \"reference\" ")
					    .append("from \"references\" ")
					    .append("where \"attribute_id\"=? and \"object_id\"=?)");
		PreparedStatement pstmt = null;
		try {
			pstmt = myConnection.dbConnection.prepareStatement(selectAttributes.toString());
			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();			
			while (rs.next()) {
				al.add(Integer.toString(rs.getInt(1)));
				tmp = rs.getString(2);
				al.add(tmp);
			}
		} finally {
			if (pstmt != null)
				pstmt.close();
		}
		return al;
	}
	
	public static ArrayList<Integer> returnIdObj(int attrId, int objId) throws SQLException {
		ArrayList<Integer> al = new ArrayList<Integer>();
		StringBuilder selectAttributes = new StringBuilder();
	    selectAttributes.append("select \"reference\" ")
						.append("from \"references\" ")
						.append("where \"attribute_id\"=? and \"object_id\"=?");
		PreparedStatement pstmt = null;
		try {
			pstmt = myConnection.dbConnection.prepareStatement(selectAttributes.toString());
			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();			
			while (rs.next()) {
				al.add(rs.getInt(1));
			}
		} finally {
			if (pstmt != null)
				pstmt.close();
		}
		return al;
	}
	
	public static ArrayList<Integer> returnLinksOnObj(int ref) throws SQLException {
		ArrayList<Integer> al = new ArrayList<Integer>();
		StringBuilder selectAttributes = new StringBuilder();
	    selectAttributes.append("select \"object_id\" ")
						.append("from \"objects\" ")
						.append("where \"object_id\" in(")
						.append("select \"object_id\" ")
						.append("from \"references\" ")
						.append("where \"reference\"=?)");
		PreparedStatement pstmt = null;
		try {
			pstmt = myConnection.dbConnection.prepareStatement(selectAttributes.toString());
			pstmt.setInt(1, ref);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();			
			while (rs.next()) {
				al.add(rs.getInt(1));
			}
		} finally {
			if (pstmt != null)
				pstmt.close();
		}
		return al;
	}

	//attrid, referen
	public static ArrayList<Integer> retRefereces(int objId) throws SQLException{
		ArrayList<Integer> ret = new ArrayList<Integer>();
		StringBuilder selectAttributes = new StringBuilder();
	    selectAttributes.append("select \"attribute_id\",\"reference\" ")
						.append("from \"references\" ")
						.append("where \"object_id\"=?");
		PreparedStatement pstmt = null;
		try {
			pstmt = myConnection.dbConnection
					.prepareStatement(selectAttributes.toString());
			pstmt.setInt(1, objId);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				ret.add(rs.getInt(1));
				ret.add(rs.getInt(2));
			}
		} finally {
			if (pstmt != null)
				pstmt.close();
		}
		return ret;
	}
}
